package com.avic.modules.scheme;

import com.avic.modules.equipment.domain.Equipment;
import com.avic.modules.equipment.domain.EquipmentCategory;
import com.avic.modules.equipment.service.mapper.EquipmentCategoryMapper;
import com.avic.modules.equipment.service.mapper.EquipmentMapper;
import com.avic.modules.mapping.domain.DataMapping;
import com.avic.modules.mapping.service.mapper.DataMappingMapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ayetony.miao
 */
@Controller
public class AutoGenerateTable {

    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String CONN_URL = "jdbc:mysql://192.168.2.153:3306/hes";
    private static final String USER = "root";
    private static final String PASS = "root";


    private static String CREATE_TABLE_NAME = "create table equipment_data_";
    private static String LEFT_BRACE = "(";
    private static String RIGHT_BRACE = ")";
    private static String ID_INCR = "id int auto_increment primary key,";
    private static String TIME_FIELD = "time datetime not null,";
    private static String FIELD_SUFFIX =  " double(12, 4) NULL DEFAULT NULL";

    private final EquipmentMapper equipmentService;
    private final DataMappingMapper dataMappingService;
    private final EquipmentCategoryMapper equipmentCategoryMapper;

    public AutoGenerateTable(EquipmentMapper equipmentService, DataMappingMapper dataMappingService, EquipmentCategoryMapper equipmentCategoryMapper) {
        this.equipmentService = equipmentService;
        this.dataMappingService = dataMappingService;
        this.equipmentCategoryMapper = equipmentCategoryMapper;
    }

    @RequestMapping("/hesTestAdmin")
    @ResponseBody
    public void main() throws SQLException {

        Connection connection = null;
        Statement statement = null;
        try{
            connection = getConnection();
            statement = connection.createStatement();

            List<String> stringList = assembleSQL();
            for (String s : stringList) {
                System.out.println(s);
                if(statement.execute(s)){
                    System.out.println("执行成功: " + s);
                }
            }
        }catch (SQLException exception) {
            exception.printStackTrace();
        }finally {
            if(connection!=null){
                connection.close();
            }
            if(statement!=null){
                statement.close();
            }
        }

    }

    private List<String> assembleSQL(){

        List<String> tables = new ArrayList<>();
        QueryWrapper<EquipmentCategory> wrapper2 = new QueryWrapper<>();
        List<EquipmentCategory> equipmentCategories = equipmentCategoryMapper.selectList(wrapper2);
        for(EquipmentCategory category:equipmentCategories){
            QueryWrapper<Equipment> criteria = new QueryWrapper<>();
            criteria.eq("equip_category_id", category.getId());
            List<Equipment> equipment = equipmentService.selectList(criteria);
            List<String> stringList = new ArrayList<>();
            for(Equipment equip:equipment){
                QueryWrapper<DataMapping> wrapper = new QueryWrapper<>();
                wrapper.eq("equip_id", equip.getId());
                List<DataMapping> dataMappings = dataMappingService.selectList(wrapper);
                for(DataMapping data:dataMappings){
                    stringList.add(data.getTagName());
                }
            }

            HashMap<String, List<String>> map = new HashMap<>();
            List<String> fields1= new ArrayList<>();
            for(String list:stringList){
                fields1.add(list);
            }

            map.put(category.getEnglishName(),fields1);


            for (Map.Entry<String, List<String>> stringListEntry : map.entrySet()) {
                StringBuilder beginSql = new StringBuilder(CREATE_TABLE_NAME + stringListEntry.getKey() + LEFT_BRACE + ID_INCR + TIME_FIELD);
                for (int i = 0; i < stringListEntry.getValue().size() - 1; i++) {
                    beginSql.append(stringListEntry.getValue().get(i)).append(FIELD_SUFFIX).append(",");
                }
                beginSql.append(stringListEntry.getValue().get(stringListEntry.getValue().size() - 1)).append(FIELD_SUFFIX);
                beginSql.append(RIGHT_BRACE);
                tables.add(beginSql.toString());
            }
        }

        return tables;
    }


    private Connection getConnection(){
        Connection connection = null;
        try{
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try{
            connection = DriverManager.getConnection(CONN_URL,USER, PASS);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return connection;
    }

}
